package com.ch.base.db;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;

import com.ch.base.factory.WrapperFactory;
import com.ch.base.obj.QueryResultObj;
import com.ch.base.obj.QuerySettingObj;
import com.ch.base.util.StringUtils;

public abstract class DBBean {
	//	boolean useDefaultConnection;
	protected Connection conn = null;
	public Logger log = Logger.getLogger(this.getClass());

	public DBBean() {
		//		useDefaultConnection = true;
	}

	public DBBean(Connection conn) {
		//		useDefaultConnection = false;
		this.conn = conn;
	}

	//	protected Connection getConnection() throws Exception {
	//		Connection conn = null ;
	//		if ( (this.conn == null) && (useDefaultConnection) ) {
	//			//			conn = ConnectionFactory.getInstance().getConnection();
	//			//			conn.setAutoCommit(false);
	//			//			this.conn = conn;
	//			//			log.info("Get DefaultConnection("+useDefaultConnection+") Success...");
	//		} else {
	//			conn = this.conn;
	//		}
	//		return conn;
	//	}

	//	protected void releaseConnection() throws SQLException {
	//		// When con is not default, the power of connection released by Calling-Object
	//		if( (conn != null) && !con.isClosed() && useDefaultConnection ) {
	//			conn.close();
	//			conn = null;
	//			log.info("Release DefaultConnection("+useDefaultConnection+") Success !! ");
	//			//			log.debug("DBBean.releaseConnection()" );
	//			//			System.out.println( "DBBean.releaseConnection()" );
	//		}
	//	}

	protected Object populate(Object obj, ResultSet rs) throws IllegalAccessException, InvocationTargetException, SQLException, InstantiationException{
		ResultSetMetaData rsMD = rs.getMetaData();
		Map<String, Object> map =  new HashMap<String, Object>();

		for(int i = 1; i <= rsMD.getColumnCount(); i++) {
			//			log.info("rs.getColumnName(i) = " + StringUtils.proper(rsMD.getColumnName(i), "_"));
			//			log.info("rs.getString(i) = " + rs.getString(i));
			//			log.info("rs.getString(i) = " + StringUtils.lowerfirst(StringUtils.proper(rsMD.getColumnName(i), "_")));

			map.put(StringUtils.lowerfirst(StringUtils.proper(rsMD.getColumnName(i), "_")), rs.getString(i) != null ? rs.getString(i) : "");
		}

		if(((Class<? extends DBBean>) obj).getSimpleName().equals("Map")){
			//			log.info("1");
			return map;
		}else{
			//			log.info("2");
			Object cloneObj = ((Class<? extends DBBean>) obj).newInstance();
			BeanUtils.populate(cloneObj, map);
			return cloneObj;

		}
	}

	public PreparedStatement getPreparedStatement(String sqlStr) throws Exception {
		//		this.conn = this.getConnection();
		try{
			return this.conn.prepareStatement(sqlStr);
		} catch (Exception ex) {
			conn.rollback();
			throw ex;
		}
	}

	/**
	 * 為PreparedStatement預編譯的SQL語句設置參數
	 * @param prep
	 * @param params 前端傳入之查詢參數
	 * @param queryCondition 取得前端傳入之 sql 語句中，有幾個 " ? "
	 * @throws Exception
	 */
	private void setPreparedStatementParams(PreparedStatement prep, Object[] params, String[] queryCondition) throws Exception {
		if(params!=null){
			for(int i=0;i<queryCondition.length;i++) {
				try{
					prep.setObject(i+1, params[i]);
				} catch (Exception ex) {
					ex.printStackTrace();
					throw ex;
				}
			}
		}
	}

	/**
	 * 取得PreparedStatement向數據庫提交的SQL語句
	 * @param sqlStr 前端傳入之SQL語句
	 * @param params 前端傳入之查詢參數
	 * @return 整理過後之PreparedStatement實體SQL語句
	 * @throws Exception
	 */
	protected String getPreparedSQL(String sqlStr, Object[] params) throws Exception {
		try {
			if(params!=null&&params.length>0){
				for(int i = 0; i < params.length; i++){
					//為了記入數字型別，數字型別之sql指令不需加入''
					if (params[i]!=null && "class java.lang.String".equalsIgnoreCase(params[i].getClass().toString())) {
						sqlStr = sqlStr.replaceFirst("\\?", "'"+params[i]+"'");
					} else  {
						sqlStr = sqlStr.replaceFirst("\\?", ""+params[i]);
					}
				}
			}
		} catch (Exception ex) {
			throw ex;
		}

		return sqlStr;
	}

//	protected String getPreparedSQL(String sqlStr, Object[] params, String groupBy, String having, String orderBy, String limit, String offset) throws Exception {
//		StringBuffer sbSql = new StringBuffer();
//
//		try {
//			if(params!=null&&params.length>0){
//				for(int i = 0; i < params.length; i++){
//					//為了記入數字型別，數字型別之sql指令不需加入''
//					if (params[i]!=null && "class java.lang.String".equalsIgnoreCase(params[i].getClass().toString())) {
//						sqlStr = sqlStr.replaceFirst("\\?", "'"+params[i]+"'");
//					} else  {
//						sqlStr = sqlStr.replaceFirst("\\?", ""+params[i]);
//					}
//				}
//			}
//
//			HttpServletRequest request;
//			request = ServletActionContext.getRequest();
//			String hostMilieu = request.getSession().getServletContext().getInitParameter("hostMilieu");
//			ResourceBundle rb = ResourceBundle.getBundle("database");
//			String database = rb.getString(StringUtils.proper(hostMilieu, "_") + "_" + request.getContextPath().replace("/", "") + "_database");
//
//			if (StringUtils.isNotEmpty(groupBy)) {
//				sqlStr += " group by " + groupBy;
//			}
//
//			if (StringUtils.isNotEmpty(having)) {
//				sqlStr += " having " + having;
//			}
//
//			if (StringUtils.isNotEmpty(orderBy) 
//					&& (database.equalsIgnoreCase("oracle") || (database.equalsIgnoreCase("sql server") && StringUtils.isEmpty(limit)))) {
//				sqlStr += " order by " + orderBy;
//			}
//
//
//
//			if (StringUtils.isNotEmpty(limit)) {
//				offset = StringUtils.isNotEmpty(offset) ? offset : "0";
//
//				if (database.equalsIgnoreCase("oracle")) {
//					/*
//					 * 參考
//					 * http://stackoverflow.com/questions/2912144/alternatives-to-limit-and-offset-for-paging-in-oracle
//					 * http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
//					 */
//					sbSql.append("select * ");
//					sbSql.append("  from ");
//					sbSql.append("(select a.*, rownum rnum ");
//					sbSql.append("   from ");
//					sbSql.append("( " + sqlStr + " ) a");
//					sbSql.append(" where rownum <= " + String.valueOf(Integer.parseInt(offset) + Integer.parseInt(limit)));
//					sbSql.append(") ");
//					sbSql.append(" where rnum >= " + offset + "; ");
//				} else if (database.equalsIgnoreCase("sql server")) {
//					orderBy = StringUtils.isNotEmpty(orderBy) ? orderBy : "SELECT 1";
//
//					sbSql.append("SELECT * ");
//					sbSql.append("  FROM (SELECT ROW_NUMBER() OVER(ORDER BY (" + orderBy + ")) as row, * ");
//					sbSql.append("          FROM ( " + sqlStr +" ) as [__inner__]) a ");
//					sbSql.append(" WHERE a.row >= " + offset + " and a.row <= " + String.valueOf(Integer.parseInt(offset) + Integer.parseInt(limit)));
//				}
//			} else {
//				sbSql.append(sqlStr);
//			}
//
//		} catch (Exception ex) {
//			throw ex;
//		}
//
//		return sbSql.toString();
//	}
	
	protected String getPreparedSQL(QuerySettingObj querySettingObj) throws Exception {
		StringBuffer sbSql = new StringBuffer();
		String sqlStr = querySettingObj.getSql();
		
		try {
			if(querySettingObj.getPreparedStatementParams() != null && querySettingObj.getPreparedStatementParams().length > 0){
				for(int i = 0; i < querySettingObj.getPreparedStatementParams().length; i++){
					//為了記入數字型別，數字型別之sql指令不需加入''
					if (querySettingObj.getPreparedStatementParams()[i]!=null && "class java.lang.String".equalsIgnoreCase(querySettingObj.getPreparedStatementParams()[i].getClass().toString())) {
						sqlStr = sqlStr.replaceFirst("\\?", "'"+querySettingObj.getPreparedStatementParams()[i]+"'");
					} else  {
						sqlStr = sqlStr.replaceFirst("\\?", ""+querySettingObj.getPreparedStatementParams()[i]);
					}
				}
			}

			HttpServletRequest request;
			request = ServletActionContext.getRequest();
			String hostMilieu = request.getSession().getServletContext().getInitParameter("hostMilieu");
			ResourceBundle rb = ResourceBundle.getBundle("database");
			String database = rb.getString(StringUtils.proper(hostMilieu, "_") + "_" + request.getContextPath().replace("/", "") + "_database");

			if (StringUtils.isNotEmpty(querySettingObj.getGroupBy())) {
				sqlStr += " group by " + querySettingObj.getGroupBy();
			}

			if (StringUtils.isNotEmpty(querySettingObj.getHaving())) {
				sqlStr += " having " + querySettingObj.getHaving();
			}

			if (StringUtils.isNotEmpty(querySettingObj.getOrderBy()) 
					&& (database.equalsIgnoreCase("oracle") || (database.equalsIgnoreCase("sql server") && StringUtils.isEmpty(querySettingObj.getLimit())))) {
				sqlStr += " order by " + querySettingObj.getOrderBy();
			}



			if (StringUtils.isNotEmpty(querySettingObj.getLimit())) {
				querySettingObj.setOffset(StringUtils.isNotEmpty(querySettingObj.getOffset()) ? querySettingObj.getOffset() : "0");

				if (database.equalsIgnoreCase("oracle")) {
					/*
					 * 參考
					 * http://stackoverflow.com/questions/2912144/alternatives-to-limit-and-offset-for-paging-in-oracle
					 * http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
					 */
					sbSql.append("select * ");
					sbSql.append("  from ");
					sbSql.append("(select a.*, rownum rnum ");
					sbSql.append("   from ");
					sbSql.append("( " + sqlStr + " ) a");
					sbSql.append(" where rownum <= " + String.valueOf(Integer.parseInt(querySettingObj.getOffset()) + Integer.parseInt(querySettingObj.getLimit())));
					sbSql.append(") ");
					sbSql.append(" where rnum >= " + querySettingObj.getOffset() + "; ");
				} else if (database.equalsIgnoreCase("sql server")) {
					querySettingObj.setOrderBy(StringUtils.isNotEmpty(querySettingObj.getOrderBy()) ? querySettingObj.getOrderBy() : "(SELECT 1)");

					sbSql.append("SELECT * ");
					sbSql.append("  FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + querySettingObj.getOrderBy() + ") as row, * ");
					sbSql.append("          FROM ( " + sqlStr +" ) as [__inner__]) a ");
					sbSql.append(" WHERE a.row > " + querySettingObj.getOffset() + " and a.row <= " + String.valueOf(Integer.parseInt(querySettingObj.getOffset()) + Integer.parseInt(querySettingObj.getLimit())));
				}
				
			} else {
				sbSql.append(sqlStr);
			}

		} catch (Exception ex) {
			throw ex;
		}

		return sbSql.toString();
	}

	// ---------------------------------------- executeQuery Start ---------------------------------------- //
	/**
	 * 確切執行 preparedStatement.executeQuery
	 * @param obj
	 * @param prep
	 * @return List 物件
	 * @throws Exception
	 */
	protected List executeQuery(Class obj, PreparedStatement prep) throws Exception {
		ResultSet rs = null;
		List result = new ArrayList();

		try {
			// 執行SQL語句
			rs = prep.executeQuery();
			while (rs.next()) {
				result.add(populate(obj, rs));
			}

		} catch (Exception ex) {
			throw ex;
		} finally {
			rs.close();
		}

		return result;
	}

	/**
	 * 執行查詢數據庫的語句
	 * @param sqlStr 前端傳入之SQL語句
	 * @param params 端傳入之查詢參數
	 * @return  <T extends WrapperFactory> QueryResultObj 物件
	 * @throws Exception
	 */
	protected QueryResultObj executeQuery(Class obj, String sqlStr, Object[] params) throws Exception {
		QueryResultObj queryResultObj = new QueryResultObj();
		PreparedStatement prep = null;
		List queryList = new ArrayList(); 

		try {
			prep = this.getPreparedStatement(sqlStr);

			// 設置SQL語句的參數
			if( (params!=null) && (params.length > 0) ) {
				this.setPreparedStatementParams(prep, params, sqlStr.substring(0, sqlStr.lastIndexOf("?")+1).split("\\?"));
			}

			// 印出SQL語句
			log.debug("executeQuery = " + this.getPreparedSQL(sqlStr, params));

			// 執行語句
			queryList = this.executeQuery(obj, prep);
			
			queryResultObj.setTotalCount(queryList.size());
			queryResultObj.setQueryList(queryList);
		} catch (Exception ex) {
			throw ex;
		} finally {
			prep.close();
		}

		return queryResultObj;
	}

	/**
	 * 執行查詢數據庫的語句
	 * @param sqlStr 前端傳入之SQL語句
	 * @return QueryResultObj 物件
	 * @throws Exception 
	 */
	//	protected QueryResultObj executeQuery(String sqlStr) throws Exception {
	//		return this.executeQuery(sqlStr, null);
	//	}

	/**
	 * 執行查詢數據庫的語句，並 取得查詢結果某一區間之資料
	 * @param sqlStr 前端傳入之SQL語句
	 * @param params 端傳入之查詢參數
	 * @param startIdx 取得查詢結果某一區間之起
	 * @param endIdx 取得查詢結果某一區間之迄
	 * @return QueryResultObj 物件
	 * @throws Exception
	 */
//	protected QueryResultObj executeQuery(Class obj, String sqlStr, Object[] params, int startIdx, int endIdx) throws Exception {
//		QueryResultObj queryResultObj = new QueryResultObj();
//		PreparedStatement prep = null;
//		ResultSet rs = null;
//		List queryList = new ArrayList(); 
//
//		try {
//			prep = this.getPreparedStatement(sqlStr);
//
//			// 設置SQL語句的參數
//			if( (params!=null) && (params.length > 0) ) {
//				this.setPreparedStatementParams(prep, params, sqlStr.substring(0, sqlStr.lastIndexOf("?")+1).split("\\?"));
//			}
//
//			// 印出SQL語句
//			log.debug("executeQuery = " + this.getPreparedSQL(sqlStr, params));
//
//			// 執行SQL語句
//			rs = prep.executeQuery();
//
//			int currIdx = startIdx - 1;
//			while ( (rs.absolute(++currIdx)) && (currIdx <= endIdx) ){
//				queryList.add(populate(obj, rs));
//			}
//
//			// 取得查詢結果之總數
//			rs.last();
//			queryResultObj.setQueryList(queryList);
//			queryResultObj.setTotalCount(rs.getRow());
//		} catch (Exception ex) {
//			throw ex;
//		}  finally {
//			rs.close();
//			prep.close();
//		}
//
//		return queryResultObj;
//	}

	/**
	 * 執行查詢數據庫的語句，此方法可取得某一區間之查詢結果
	 * @param sqlStr	前端傳入之SQL語句
	 * @param startIdx 取得查詢結果某一區間之起
	 * @param endIdx 取得查詢結果某一區間之迄
	 * @return QueryResultObj 物件
	 * @throws Exception
	 */
//	protected QueryResultObj executeQuery(Class obj, String sqlStr, int startIdx, int endIdx) throws Exception {
//		return this.executeQuery(obj, sqlStr, null, startIdx, endIdx);
//	}

//	protected QueryResultObj executeQuery(Class obj, String sqlStr, Object[] params, String groupBy, String having, String orderBy, String limit, String offset) throws Exception {
//		QueryResultObj queryResultObj = new QueryResultObj();
//		int totalCount = 0;
//		List queryList = new ArrayList(); 
//		PreparedStatement prep = null;
//		
//		try {
//			HttpServletRequest request;
//			request = ServletActionContext.getRequest();
//			String hostMilieu = request.getSession().getServletContext().getInitParameter("hostMilieu");
//			ResourceBundle rb = ResourceBundle.getBundle("database");
//			String database = rb.getString(StringUtils.proper(hostMilieu, "_") + "_" + request.getContextPath().replace("/", "") + "_database");
//
//			if (StringUtils.isNotEmpty(groupBy)) {
//				sqlStr += " group by " + groupBy;
//			}
//
//			if (StringUtils.isNotEmpty(having)) {
//				sqlStr += " having " + having;
//			}
//
//			if (StringUtils.isNotEmpty(orderBy) 
//					&& (database.equalsIgnoreCase("oracle") || (database.equalsIgnoreCase("sql server") && StringUtils.isEmpty(limit)))) {
//				sqlStr += " order by " + orderBy;
//			}
//
//			StringBuffer sbSql = new StringBuffer();
//
//			if (StringUtils.isNotEmpty(limit)) {
//				offset = StringUtils.isNotEmpty(offset) ? offset : "0";
//
//				if (database.equalsIgnoreCase("oracle")) {
//					/*
//					 * 參考
//					 * http://stackoverflow.com/questions/2912144/alternatives-to-limit-and-offset-for-paging-in-oracle
//					 * http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
//					 */
//					sbSql.append("select * ");
//					sbSql.append("  from ");
//					sbSql.append("(select a.*, rownum rnum ");
//					sbSql.append("   from ");
//					sbSql.append("( " + sqlStr + " ) a");
//					sbSql.append(" where rownum <= " + String.valueOf(Integer.parseInt(offset) + Integer.parseInt(limit)));
//					sbSql.append(") ");
//					sbSql.append(" where rnum >= " + offset + "; ");
//				} else if (database.equalsIgnoreCase("sql server")) {
//					orderBy = StringUtils.isNotEmpty(orderBy) ? orderBy : "SELECT 1";
//
//					sbSql.append("SELECT * ");
//					sbSql.append("  FROM (SELECT ROW_NUMBER() OVER(ORDER BY (" + orderBy + ")) as row, * ");
//					sbSql.append("          FROM ( " + sqlStr +" ) as [__inner__]) a ");
//					sbSql.append(" WHERE a.row > " + offset + " and a.row <= " + String.valueOf(Integer.parseInt(offset) + Integer.parseInt(limit)));
//				}
//				
//				totalCount = this.getTotalCount(sqlStr, params);
//			} else {
//				sbSql.append(sqlStr);
//			}
//
//			
//
//			prep = this.getPreparedStatement(sbSql.toString());
//
//			// 設置SQL語句的參數
//			if( (params!=null) && (params.length > 0) ) {
//				this.setPreparedStatementParams(prep, params, sqlStr.substring(0, sqlStr.lastIndexOf("?")+1).split("\\?"));
//			}
//
//			// 印出SQL語句
//			log.debug("executeQuery = " + this.getPreparedSQL(sbSql.toString(), params, groupBy, having, orderBy, limit, offset));
//
//			// 執行語句
//			queryList = this.executeQuery(obj, prep);
//			
//			if (StringUtils.isEmpty(limit)) {
//				totalCount = queryList.size();
//			}
//			
//			queryResultObj.setTotalCount(totalCount);
//			queryResultObj.setQueryList(queryList);
//		} catch (Exception ex) {
//			throw ex;
//		}  finally {
//			prep.close();
//		}
//		
//		return queryResultObj;
//	}
	
	protected QueryResultObj executeQuery(QuerySettingObj querySettingObj) throws Exception {
		QueryResultObj queryResultObj = new QueryResultObj();
		int totalCount = 0;
		List queryList = new ArrayList(); 
		PreparedStatement prep = null;
		
		try {
			HttpServletRequest request;
			request = ServletActionContext.getRequest();
			String hostMilieu = request.getSession().getServletContext().getInitParameter("hostMilieu");
			ResourceBundle rb = ResourceBundle.getBundle("database");
			String database = rb.getString(StringUtils.proper(hostMilieu, "_") + "_" + request.getContextPath().replace("/", "") + "_database");
			String sqlStr = querySettingObj.getSql();
			
			if (StringUtils.isNotEmpty(querySettingObj.getGroupBy())) {
				sqlStr += " group by " + querySettingObj.getGroupBy();
			}

			if (StringUtils.isNotEmpty(querySettingObj.getHaving())) {
				sqlStr += " having " + querySettingObj.getHaving();
			}

			if (StringUtils.isNotEmpty(querySettingObj.getOrderBy()) 
					&& (database.equalsIgnoreCase("oracle") || (database.equalsIgnoreCase("sql server") && StringUtils.isEmpty(querySettingObj.getLimit())))) {
				sqlStr += " order by " + querySettingObj.getOrderBy();
			}

			StringBuffer sbSql = new StringBuffer();

			if (StringUtils.isNotEmpty(querySettingObj.getLimit())) {
				querySettingObj.setOffset(StringUtils.isNotEmpty(querySettingObj.getOffset()) ? querySettingObj.getOffset() : "0");

				if (database.equalsIgnoreCase("oracle")) {
					/*
					 * 參考
					 * http://stackoverflow.com/questions/2912144/alternatives-to-limit-and-offset-for-paging-in-oracle
					 * http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
					 */
					sbSql.append("select * ");
					sbSql.append("  from ");
					sbSql.append("(select a.*, rownum rnum ");
					sbSql.append("   from ");
					sbSql.append("( " + sqlStr + " ) a");
					sbSql.append(" where rownum <= " + String.valueOf(Integer.parseInt(querySettingObj.getOffset()) + Integer.parseInt(querySettingObj.getLimit())));
					sbSql.append(") ");
					sbSql.append(" where rnum >= " + querySettingObj.getOffset() + "; ");
				} else if (database.equalsIgnoreCase("sql server")) {
					querySettingObj.setOrderBy(StringUtils.isNotEmpty(querySettingObj.getOrderBy()) ? querySettingObj.getOrderBy() : "SELECT 1");

					sbSql.append("SELECT * ");
					sbSql.append("  FROM (SELECT ROW_NUMBER() OVER(ORDER BY (" + querySettingObj.getOrderBy() + ")) as row, * ");
					sbSql.append("          FROM ( " + sqlStr +" ) as [__inner__]) a ");
					sbSql.append(" WHERE a.row > " + querySettingObj.getOffset() + " and a.row <= " + String.valueOf(Integer.parseInt(querySettingObj.getOffset()) + Integer.parseInt(querySettingObj.getLimit())));
				}
				
				totalCount = this.getTotalCount(sqlStr, querySettingObj.getPreparedStatementParams());
			} else {
				sbSql.append(sqlStr);
			}

			

			prep = this.getPreparedStatement(sbSql.toString());

			// 設置SQL語句的參數
			if( (querySettingObj.getPreparedStatementParams()!=null) && (querySettingObj.getPreparedStatementParams().length > 0) ) {
				this.setPreparedStatementParams(prep, querySettingObj.getPreparedStatementParams(), sqlStr.substring(0, sqlStr.lastIndexOf("?")+1).split("\\?"));
			}

			// 印出SQL語句
//			log.debug("executeQuery = " + this.getPreparedSQL(sbSql.toString(), params, groupBy, having, orderBy, limit, offset));

			// 執行語句
			queryList = this.executeQuery(querySettingObj.getResultType(), prep);
			
			if (StringUtils.isEmpty(querySettingObj.getLimit())) {
				totalCount = queryList.size();
			}
			
			queryResultObj.setTotalCount(totalCount);
			queryResultObj.setQueryList(queryList);
		} catch (Exception ex) {
			throw ex;
		}  finally {
			prep.close();
		}
		
		return queryResultObj;
	}

	protected int getTotalCount(String sqlStr, Object[] params) throws Exception {
		String queryTotalCount = "0";
		PreparedStatement prep = null;
		try {
			sqlStr = "select count(*) as total_count from ( " + sqlStr + " ) t ";
			//			
			prep = this.getPreparedStatement(sqlStr);

			// 設置SQL語句的參數
			if( (params!=null) && (params.length > 0) ) {
				this.setPreparedStatementParams(prep, params, sqlStr.substring(0, sqlStr.lastIndexOf("?")+1).split("\\?"));
			}

			// 印出SQL語句
			log.debug("executeQuery = " + this.getPreparedSQL(sqlStr, params));

			// 執行語句
			queryTotalCount = (String) ((Map)this.executeQuery(Map.class, prep).get(0)).get("totalCount");
		} catch (Exception ex) {
			throw ex;
		}  finally {
			prep.close();
		}
		
		return Integer.parseInt(queryTotalCount);
	}
	// ---------------------------------------- executeQuery End ---------------------------------------- //



	// --------------------------------------- executeUpdate Start --------------------------------------- //
	/**
	 * 確切執行 preparedStatement.executeUpdate
	 * @param prep
	 * @return 異動成功的資料筆數
	 * @throws Exception
	 */
	protected int executeUpdate(PreparedStatement prep) throws Exception {
		int result = -1;
		
		try {
			result = prep.executeUpdate();
		} catch (Exception ex) {
			throw ex;
		}

		return result;
	}

	/**
	 * 執行異動數據庫的語句
	 * @param sqlStr 前端傳入之SQL語句
	 * @param params 前端傳入之異動參數
	 * @return 異動成功的資料筆數
	 * @throws Exception 
	 */
	protected int executeUpdate(String sqlStr, Object[] params) throws Exception {
		int result = -1;
		PreparedStatement prep = null;
		try {
			prep = this.getPreparedStatement(sqlStr);

			// 設置SQL語句的參數
			if( (params!=null) && (params.length > 0) ) {
				this.setPreparedStatementParams(prep, params, sqlStr.substring(0, sqlStr.lastIndexOf("?")+1).split("\\?"));
			}

			// 印出SQL語句
			String entitySql = this.getPreparedSQL(sqlStr, params);
			log.debug("executeUpdate = " + entitySql);

			// 執行語句
			result = this.executeUpdate(prep);

			// 寫SQL語句之log進DB
			this.insertSqlLog(entitySql);
		} catch (Exception ex) {
			throw ex;
		} finally {
			prep.close();
		}

		return result;
	}

	/**
	 * 執行異動數據庫的語句
	 * @param sqlStr 前端傳入之SQL語句
	 * @return 異動成功的資料筆數
	 * @throws Exception
	 */
//	protected int executeUpdate(String sqlStr) throws Exception {
//		return this.executeUpdate(sqlStr, null);
//	}
	// --------------------------------------- executeUpdate End --------------------------------------- //



	private void insertSqlLog(String sqlStr) throws Exception {
		//		SysSqlLogInfoWrapper sysSqlLogInfoWrapper = new SysSqlLogInfoWrapper();
		HttpServletRequest request = ServletActionContext.getRequest();
		HttpSession session = request.getSession();

		//		sysSqlLogInfoWrapper = (SysSqlLogInfoWrapper) session.getAttribute("sysSqlLogInfo");

		String tableName = "";
		String updateType = "";
		String tempSqlStr = "";

		int tempSqlNum = 0;
		if(sqlStr.toLowerCase().indexOf("insert") > -1){
			updateType = "I";

			tempSqlStr = sqlStr.substring(sqlStr.toLowerCase().indexOf("into") + 5);
			for(int i = 0; i < tempSqlStr.length(); i++){
				if( !tempSqlStr.substring(i, i+1).equals(" ")){
					tempSqlNum = i;
					break;
				}
			}
			tempSqlStr = tempSqlStr.substring(tempSqlNum);
			for(int i = 0; i < tempSqlStr.length(); i++){
				if(( tempSqlStr.substring(i, i+1).equals(" ") || tempSqlStr.substring(i, i+1).equals("(") )){
					tempSqlNum = i;
					break;
				}
			}
			tableName = tempSqlStr.substring(0, tempSqlNum);
			log.debug("DBBean insertSqlLog insert tableName: " + tableName);
		}else if(sqlStr.toLowerCase().indexOf("update") > -1 && sqlStr.toLowerCase().indexOf("update") < 10){
			//sqlStr.toLowerCase().indexOf("update") < 10  主要避免 update_type 等相似欄位
			updateType = "U";

			tempSqlStr = sqlStr.substring(sqlStr.toLowerCase().indexOf("update") + 7);
			for(int i = 0; i < tempSqlStr.length(); i++){
				if( !tempSqlStr.substring(i, i+1).equals(" ") ){
					tempSqlNum = i;
					break;
				}
			}

			tempSqlStr = tempSqlStr.substring(tempSqlNum);
			tableName = tempSqlStr.substring(0, tempSqlStr.indexOf(" "));
			log.debug("DBBean insertSqlLog update tableName: " + tableName);
			//		}else if(sqlStr.toLowerCase().indexOf("delete from") > -1){
			//			updateType = "D";
			//			
			//			tempSqlStr = sqlStr.substring(sqlStr.toLowerCase().indexOf("delete from")+12);
			//			for(int i = 0; i < tempSqlStr.length(); i++){
			//				if( !tempSqlStr.substring(i, i+1).equals(" ") ){
			//					tempSqlNum = i;
			//					break;
			//				}
			//			}
			//			tempSqlStr = tempSqlStr.substring(tempSqlNum);
			//			tableName = tempSqlStr.substring(0, tempSqlStr.indexOf(" "));
			//			log.debug("DBBean insertSqlLog delete from tableName:" + tableName);
		}else if(sqlStr.toLowerCase().indexOf("delete") > -1){
			updateType = "D";
			//判斷是否為 delete from
			if(sqlStr.toLowerCase().indexOf("from") > -1 && sqlStr.toLowerCase().indexOf("from") < 10){
				//sqlStr.toLowerCase().indexOf("from") < 10  主要避免 sql 語句中存在 "from"
				tempSqlStr = sqlStr.substring(sqlStr.toLowerCase().indexOf("from") + 5);
			}else{
				tempSqlStr = sqlStr.substring(sqlStr.toLowerCase().indexOf("delete") + 7);
			}

			for(int i = 0; i < tempSqlStr.length(); i++){
				if( !tempSqlStr.substring(i, i+1).equals(" ") ){
					tempSqlNum = i;
					break;
				}
			}

			tempSqlStr = tempSqlStr.substring(tempSqlNum);
			tableName = tempSqlStr.substring(0, tempSqlStr.indexOf(" "));
			log.debug("DBBean insertSqlLog delete tableName:" + tableName);
		}

		String sql1 = "";
		String sql2 = "";
		String sql3 = "";
		if(sqlStr.length() <= 4000){
			sql1 = sqlStr;
		}else{
			sql1 = sqlStr.substring(0, 4000);
			if(sqlStr.length() <= 8000){
				sql2 = sqlStr.substring(4000, sqlStr.length());
			}else{
				sql2 = sqlStr.substring(4000, 8000);
				sql3 = sqlStr.substring(8000, sqlStr.length());
			}
		}
		//		sysSqlLogInfoWrapper.setTableName(tableName);
		//		sysSqlLogInfoWrapper.setSql1(sql1);
		//		sysSqlLogInfoWrapper.setSql2(sql2);
		//		sysSqlLogInfoWrapper.setSql3(sql3);
		//		LoginObj loginObj = (LoginObj)OtherUtil.getSession("loginObj");
		//		sysSqlLogInfoWrapper.setLoginIp(loginObj.getLoginIp());
		//		sysSqlLogInfoWrapper.setLoginDeptCode(loginObj.getLoginDeptCode());
		//		sysSqlLogInfoWrapper.setLoginUserId(loginObj.getLoginUserId());
		//		sysSqlLogInfoWrapper.setLoginUserCardType(loginObj.getLoginUserCardType());
		//		sysSqlLogInfoWrapper.setHandleDeptCode(loginObj.getHandleDeptCode());
		//		sysSqlLogInfoWrapper.setHandleUserId(loginObj.getHandleUserId());
		//		sysSqlLogInfoWrapper.setHandleUserCardType(loginObj.getHandleUserCardType());
		//		sysSqlLogInfoWrapper.setUpdateType(updateType);
		//		sysSqlLogInfoWrapper.setUpdateTime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Timestamp(new Date().getTime())));
		//
		//		SysSqlLogInfoDAO sysSqlLogInfoDAO  = new SysSqlLogInfoDAO(conn);
		//		sysSqlLogInfoDAO.insert(sysSqlLogInfoWrapper);
	}

	//	public boolean isUseDefaultConnection() {
	//		return useDefaultConnection;
	//	}
	//
	//	public void setUseDefaultConnection(boolean useDefaultConnection) {
	//		this.useDefaultConnection = useDefaultConnection;
	//	}

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public void setLogger(String loggerName) {
		log = Logger.getLogger(loggerName);
	}

}
